﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Common.Property;
using Common.Utility;
using Dapper;
using DapperExtensions;
using DapperExtensions.Predicate;
using Domain;

namespace Infrastructure.Dapper
{
    public class RepositoryBase<T> : IRepositoryBase<T> where T : BaseEntity
    {
        #region 同步
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="primaryId"></param>
        /// <returns></returns>
        public T GetById(string id)
        {
            var tblName = string.Format("dbo.{0}", typeof(T).Name);
            var sql = string.Format("SELECT * FROM {0} WHERE IsDelete = 0 AND Id = @Id", tblName);

            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                return conn.QueryFirstOrDefault<T>(sql, new { Id = id });
            }
        }
        #endregion

        #region 异步
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="primaryId"></param>
        /// <returns></returns>
        public async Task<T> GetByIdAsync(string id)
        {
            var tblName = string.Format("dbo.{0}", typeof(T).Name);
            var sql = string.Format("SELECT * FROM {0} WHERE IsDelete = 0 AND Id = @Id", tblName);

            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                return await conn.QueryFirstOrDefaultAsync<T>(sql, new { Id = id });
            }
        }
        /// <summary>
        /// 根据多个Id获取多个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task<IEnumerable<T>> GetByIdsAsync(IList<string> ids)
        {
            if (!ids.Any())
            {
                return new List<T>();
            }
            var tblName = string.Format("dbo.{0}", typeof(T).Name);
            var sql = string.Format("SELECT * FROM {0} WHERE IsDelete = 0 AND Id in @ids", tblName);

            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                IEnumerable<T> dataList = await conn.QueryAsync<T>(sql, new { ids = ids.ToArray() });
                return dataList;
            }
        }

        /// <summary>
        /// 获取全部数据集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public async Task<IEnumerable<T>> GetAllAsync()
        {
            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                var sort = new List<ISort>();
                sort.Add(new Sort { PropertyName = "CreateTime", Ascending = false });

                var pgb = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                pgb.Predicates.Add(Predicates.Field<T>(f => f.IsDelete, Operator.Eq, 0));
                return await conn.GetListAsync<T>(pgb);
            }
        }

        /// <summary>
        /// 统计记录总数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public async Task<int> CountAsync(object predicate)
        {
            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                PredicateGroup pgb;
                if (predicate == null)
                {
                    pgb = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                    pgb.Predicates.Add(Predicates.Field<T>(f => f.IsDelete, Operator.Eq, 0));
                }
                else
                {
                    pgb = (PredicateGroup)predicate;
                    pgb.Predicates.Add(Predicates.Field<T>(f => f.IsDelete, Operator.Eq, 0));
                }
                return await conn.CountAsync<T>(predicate);
            }
        }

        /// <summary>
        /// 获取全部数据集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public async Task<T> GetFirstOrDefaultAsync(object predicate, IList<ISort> sort = null)
        {
            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                if (sort == null)
                {
                    sort = new List<ISort>();
                    sort.Add(new Sort { PropertyName = "CreateTime", Ascending = false });
                }

                PredicateGroup pgb;
                if (predicate == null)
                {
                    pgb = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                    pgb.Predicates.Add(Predicates.Field<T>(f => f.IsDelete, Operator.Eq, 0));
                }
                else
                {
                    pgb = (PredicateGroup)predicate;
                    pgb.Predicates.Add(Predicates.Field<T>(f => f.IsDelete, Operator.Eq, 0));
                }
                var list = await conn.GetListAsync<T>(pgb, sort, null, null);
                return list.FirstOrDefault();
            }
        }

        /// <summary>
        /// 获取全部数据集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public async Task<T> GetFirstOrDefaultHasDeleteAsync(object predicate, IList<ISort> sort = null)
        {
            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                if (sort == null)
                {
                    sort = new List<ISort>();
                    sort.Add(new Sort { PropertyName = "CreateTime", Ascending = false });
                }
                var list = await conn.GetListAsync<T>(predicate, sort, null, null);
                return list.FirstOrDefault();
            }
        }

        /// <summary>
        /// 查询列表数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate">条件</param>
        /// <param name="sort">排序</param>
        /// <param name="buffered">缓存</param>
        /// <returns></returns>
        public async Task<IEnumerable<T>> GetListAsync(object predicate = null, IList<ISort> sort = null)
        {
            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                if (sort == null)
                {
                    sort = new List<ISort>();
                    sort.Add(new Sort { PropertyName = "CreateTime", Ascending = false });
                }

                PredicateGroup pgb;
                if (predicate == null)
                {
                    pgb = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                    pgb.Predicates.Add(Predicates.Field<T>(f => f.IsDelete, Operator.Eq, 0));
                }
                else
                {
                    pgb = (PredicateGroup)predicate;
                    pgb.Predicates.Add(Predicates.Field<T>(f => f.IsDelete, Operator.Eq, 0));
                }
                return await conn.GetListAsync<T>(pgb, sort, null, null);
            }
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="allRowsCount"></param>
        /// <param name="predicate"></param>
        /// <param name="sort"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public async Task<Tuple<IEnumerable<T>, int>> GetPageListAsync(int pageIndex, int pageSize, object predicate = null, IList<ISort> sort = null)
        {
            if (sort == null)
            {
                sort = new List<ISort>();
                sort.Add(new Sort { PropertyName = "CreateTime", Ascending = false });
            }

            PredicateGroup pgb;
            if (predicate == null)
            {
                pgb = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                pgb.Predicates.Add(Predicates.Field<T>(f => f.IsDelete, Operator.Eq, 0));
            }
            else
            {
                pgb = (PredicateGroup)predicate;
                pgb.Predicates.Add(Predicates.Field<T>(f => f.IsDelete, Operator.Eq, 0));
            }

            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                var entityList = await conn.GetPageAsync<T>(pgb, sort, pageIndex, pageSize, null, null);
                var totalCount = await conn.CountAsync<T>(pgb);
                return new Tuple<IEnumerable<T>, int>(entityList, totalCount);
            }
        }

        /// <summary>
        /// 插入单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public async Task<bool> InsertAsync(T entity, OperatorInfo op)
        {
            if (string.IsNullOrWhiteSpace(entity.Id))
            {
                entity.Id = Guid.NewGuid().ToString();
            }
            entity.IsDelete = 0;
            entity.CreateUserId = op.UserId;
            entity.CreateTime = DateTime.Now;
            entity.UpdateUserId = op.UserId;
            entity.UpdateTime = DateTime.Now;
            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                var result = await conn.InsertAsync<T>(entity);
                return !string.IsNullOrWhiteSpace(result);
            }
        }

        /// <summary>
        /// 更新单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public async Task<bool> UpdateAsync(T entity, OperatorInfo op)
        {
            entity.UpdateUserId = op.UserId;
            entity.UpdateTime = DateTime.Now;
            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                return await conn.UpdateAsync<T>(entity);
            }
        }

        /// <summary>
        /// 删除单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(string id, OperatorInfo op)
        {
            var tblName = string.Format("dbo.{0}", typeof(T).Name);
            var sql = string.Format("UPDATE {0} SET IsDelete = 1, UpdateUserId = @UpdateUserId, UpdateTime = @UpdateTime WHERE Id = @id", tblName);

            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                int count = await conn.ExecuteAsync(sql, new { id = id, UpdateUserId = op.UserId, UpdateTime = DateTime.Now });
                return count > 0;
            }
        }
        /// <summary>
        /// 删除单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public async Task<bool> DeleteListAsync(object predicate, OperatorInfo op)
        {
            var entityList = await GetListAsync(predicate);
            return await DeleteBatchAsync(entityList.Select(c => c.Id), op);
        }

        /// <summary>
        /// 批量插入功能
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityList"></param>
        /// <param name="transaction"></param>
        public async Task<bool> InsertBatchAsync(IEnumerable<T> entityList, OperatorInfo op)
        {
            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                var transection = conn.BeginTransaction();
                try
                {
                    foreach (T item in entityList)
                    {
                        if (string.IsNullOrWhiteSpace(item.Id))
                        {
                            item.Id = Guid.NewGuid().ToString();
                        }
                        item.IsDelete = 0;
                        item.CreateUserId = op.UserId;
                        item.CreateTime = DateTime.Now;
                        item.UpdateUserId = op.UserId;
                        item.UpdateTime = DateTime.Now;
                        await conn.InsertAsync<T>(item, transection);
                    }
                    transection.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    LogHelper.Error(ex);
                    transection.Rollback();
                    return false;
                }
            }
        }
        /// <summary>
        /// 批量更新（）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityList"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public async Task<bool> UpdateBatchAsync(IEnumerable<T> entityList, OperatorInfo op)
        {
            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                var transection = conn.BeginTransaction();
                try
                {
                    foreach (T item in entityList)
                    {
                        item.UpdateUserId = op.UserId;
                        item.UpdateTime = DateTime.Now;
                        await conn.UpdateAsync<T>(item, transection);
                    }
                    transection.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    LogHelper.Error(ex);
                    transection.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ids"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public async Task<bool> DeleteBatchAsync(IEnumerable<dynamic> ids, OperatorInfo op)
        {
            var tblName = string.Format("dbo.{0}", typeof(T).Name);
            var sql = string.Format("UPDATE {0} SET IsDelete = 1, UpdateUserId = @UpdateUserId, UpdateTime = @UpdateTime WHERE Id in @ids", tblName);

            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                int count = await conn.ExecuteAsync(sql, new { table = tblName, ids = ids.ToArray(), UpdateUserId = op.UserId, UpdateTime = DateTime.Now });
                return count > 0;
            }
        }

        /// <summary>
        /// 获取最新流水号
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="key">关键字</param>
        /// <param name="length">流水号长度</param>
        /// <returns></returns>
        public async Task<string> GetNewNoAsync(string tableName, string columnName, string key, int length = 3)
        {
            string sql = string.Format(@"SELECT MAX({1}) FROM {0} WHERE {1} LIKE @Key", tableName, columnName);
            var param = new DynamicParameters();
            param.Add("@Key", key + "%");

            using (IDbConnection conn = ConnectionFactory.CreateSqlConnection())
            {
                var maxNo = await conn.QueryFirstOrDefaultAsync<string>(sql, param);
                if (string.IsNullOrWhiteSpace(maxNo))
                {
                    return key + "1".PadLeft(length, '0');
                }
                var serialNumber = Convert.ToInt32(maxNo.Replace(key, "")) + 1;
                return key + serialNumber.ToString().PadLeft(length, '0');
            }
        }
        #endregion

        protected string GetDepartmentsQuery(string prefix, List<string> departments)
        {
            var sql = new StringBuilder("");
            departments.ForEach(depart => sql.Append(@$" OR ','+{prefix}Department+',' LIKE '%,{depart},%'"));
            sql.Remove(0, 3);
            sql.Insert(0, " AND (");
            sql.Append(")");
            return sql.ToString();
        }
    }
}
